package com.bsoft.gol.hcs.utils.excel;


import com.alibaba.fastjson.JSON;
import com.bsoft.gol.hcs.satisfaction.bean.response.SurveyPlanTitleListResponse;
import com.bsoft.gol.hcs.satisfaction.bean.response.SurveyPlanWorkListResponse;
import com.bsoft.gol.hcs.satisfaction.bean.response.SurveyPlanWorkListResponseNew;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lidaqian
 */
public class SatisfactionSurveyExcelUtil {

    private final static Logger logger = LoggerFactory.getLogger(SatisfactionSurveyExcelUtil.class);

    private static CellStyle style;




    public static byte[] getExcelAsByteArrayNew(List<SurveyPlanWorkListResponseNew> list) {
        if (list == null) {
            throw new NullPointerException();
        }

        List<SurveyPlanWorkListResponse> lst2 = new ArrayList<>();
        for (SurveyPlanWorkListResponseNew surveyPlanWorkListResponseNew : list) {
            lst2.addAll(surveyPlanWorkListResponseNew.getSurveyPlanWorkListResponses());
        }

        Workbook wb;
        ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("满意度评价");

            initCellStyle(wb);
            initHeadStyle(sheet, list.size());
            initHeadContent(lst2, sheet);
            initBodyNew(list, sheet);

            wb.write(out);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return out.toByteArray();
    }




    /**
     * 生成汇总Excel
     *
     * @throws NullPointerException
     */
    public static byte[] getExcelAsByteArray(List<SurveyPlanWorkListResponse> list) {
        if (list == null) {
            throw new NullPointerException();
        }
        Workbook wb;
        ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("满意度评价");

            initCellStyle(wb);
            initHeadStyle(sheet, list.size());
            initHeadContent(list, sheet);
            initBody(list, sheet);

            wb.write(out);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return out.toByteArray();
    }


    private static void initBodyNew(List<SurveyPlanWorkListResponseNew> listNew, Sheet sheet) {
        logger.info("-------->listNew:{}", JSON.toJSONString(listNew));
        List<SurveyPlanWorkListResponse> list =new ArrayList<>();
        //int lastSize = 3;
        List<Integer> sizeList = new ArrayList<>();
        for (int i = 0; i< listNew.size(); i++){
            SurveyPlanWorkListResponseNew summary = listNew.get(i);
            list.addAll(summary.getSurveyPlanWorkListResponses());
            sizeList.add(summary.getSurveyPlanWorkListResponses().size());
            logger.info("-------->sizeList:{}", JSON.toJSONString(sizeList));
        }
        logger.info("-------->sizeListEnd:{}", JSON.toJSONString(sizeList));

        for (int i = 0; i< list.size(); i++) {
            Row row = sheet.createRow(i + 3);
            SurveyPlanWorkListResponse summary = list.get(i);
            Double push =Double.valueOf(summary.getPush());
            Double back;
            if (summary.getBack()==null){
                back = Double.valueOf("0");
            }else {
                back = Double.valueOf(summary.getBack());
            }
            DecimalFormat decimalFormat = new DecimalFormat("#.00");
            String result=   decimalFormat.format((back/push)*100);
//            setCellValue(row, 0, style, "" + summary.getOrgName());
            if (summary.getBack()==null){
                summary.setBack("0");
            }
            if (result.equals(".00")){
                result="0.00";
            }
            if (result.charAt(0)=='.'){
                result = 0+result;
            }
            setCellValue(row, 0, style, "" + summary.getDeptPidName());//科室归属
            setCellValue(row, 1, style, "" + summary.getDepartmentName()); //科室
            setCellValue(row, 2, style, "" + summary.getPush());  //推送量
            setCellValue(row, 3, style, ""+summary.getBack());  //回填量
            setCellValue(row, 4, style, ""+result);    //回填百分比
            setCellValue(row,5,style,summary.getC());  //满意度百分比
            setCellValue(row,6,style,summary.getD()); //基本满意百分比
            setCellValue(row,7,style,summary.getE()); //不满意百分比

            List<SurveyPlanTitleListResponse> listResponses = list.get(i).getSurveyPlanTitleListResponses();
            if(listResponses.size()>0) {
                for (int j = 0; j < listResponses.size(); j++) {
                    SurveyPlanTitleListResponse surveyPlanTitle = listResponses.get(j);
                    BigDecimal num = new BigDecimal(surveyPlanTitle.getAverage());
                    BigDecimal bnum = num.setScale(2, RoundingMode.HALF_UP);
                    setCellValue(row, j+4, style, bnum.toString());
                }
            }
        }
        Integer firstRow = 3;
        for(int i = 0; i< sizeList.size(); i++){
            logger.info("firstRow:{},lastRow:{}",firstRow,firstRow+sizeList.get(i)-1);
            if(sizeList.get(i)>1) {
                sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow + sizeList.get(i) - 1, 0, 0));
            }
            firstRow = firstRow+sizeList.get(i);
        }
//        for (int i = 0; i< listNew.size(); i++){
//            SurveyPlanWorkListResponseNew summary = listNew.get(i);
//            int size = summary.getSurveyPlanWorkListResponses().size();
//            logger.info("lastSize:{}",lastSize);
//            Row row = sheet.createRow(lastSize);
//            setCellValue(row, 0, style, "" + summary.getDeptPidName());
//            if (size > 1){
//                sheet.addMergedRegion(new CellRangeAddress(lastSize, 2 + size + lastSize , 0, 0));
//            }
//            lastSize = 3+size;
//        }
    }




    private static void initBody(List<SurveyPlanWorkListResponse> list, Sheet sheet) {
        for (int i = 0; i< list.size(); i++) {
            Row row = sheet.createRow(i + 3);
            SurveyPlanWorkListResponse summary = list.get(i);
            Double push =Double.valueOf(summary.getPush());
            Double back;
            if (summary.getBack()==null){
                back = Double.valueOf("0");
            }else {
                back = Double.valueOf(summary.getBack());
            }
            DecimalFormat decimalFormat = new DecimalFormat("#.00");
            String result=   decimalFormat.format((back/push)*100);
//            setCellValue(row, 0, style, "" + summary.getOrgName());
            if (summary.getBack()==null){
                summary.setBack("0");
            }
            if (result.equals(".00")){
                result="0.00";
            }
            if (result.charAt(0)=='.'){
                result = 0+result;
            }
            setCellValue(row, 0, style, "" + summary.getDeptPidName());//科室归属
            setCellValue(row, 1, style, "" + summary.getDepartmentName()); //科室
            setCellValue(row, 2, style, "" + summary.getPush());  //推送量
            setCellValue(row, 3, style, ""+summary.getBack());  //回填量
            setCellValue(row, 4, style, ""+result);    //回填百分比
            setCellValue(row,5,style,summary.getC());  //满意度百分比
            setCellValue(row,6,style,summary.getD()); //基本满意百分比
            setCellValue(row,7,style,summary.getE()); //不满意百分比

            List<SurveyPlanTitleListResponse> listResponses = list.get(i).getSurveyPlanTitleListResponses();
            if(listResponses.size()>0) {
                for (int j = 0; j < listResponses.size(); j++) {
                    SurveyPlanTitleListResponse surveyPlanTitle = listResponses.get(j);
                    BigDecimal num = new BigDecimal(surveyPlanTitle.getAverage());
                    BigDecimal bnum = num.setScale(2, RoundingMode.HALF_UP);
                    setCellValue(row, j+4, style, bnum.toString());
                }
            }

//            if (CommonUtils.isNotEmpty(summary.getLogCdate())) {
//                setCellValue(row, 5, style, DateUtils.toString(summary.getLogCdate(), "yyyy-MM-dd HH:mm:ss") + "");
//            } else {
//                setCellValue(row, 5, style, "");
//            }
//            if (CommonUtils.isNotEmpty(summary.getLogClentOS())) {
//                setCellValue(row, 6, style, "" + summary.getLogClentOS());
//            } else {
//                setCellValue(row, 6, style, "");
//            }
//            setCellValue(row, 7, style, summary.getLogIp() + "");
//            setCellValue(row, 8, style, summary.getLogDesc() + "");
           /* setCellValue(row, 7, style, ""+summary.getVideoTotalFee());
            setCellValue(row, 8, style, summary.getReturnVisitCount()+"");
            setCellValue(row, 9, style, ""+summary.getReturnVisitTotalFee());
            setCellValue(row, 10, style, summary.getAssistClinicCount()+"");
            setCellValue(row, 11, style, ""+summary.getAssistClinicTotalFee());
            setCellValue(row, 12, style, String.valueOf(summary.getTotalFee()));
            setCellValue(row, 13, style, String.valueOf(summary.getAvgScore()));*/
        }
    }

    private static void initBody2(List<SurveyPlanWorkListResponse> list, Sheet sheet) {
        for (int i = 0; i< list.size(); i++) {
            Row row = sheet.createRow(i + 3);
            SurveyPlanWorkListResponse summary = list.get(i);
            Double push =Double.valueOf(summary.getPush());
            Double back;
            if (summary.getBack()==null){
                 back = Double.valueOf("0");
            }else {
                 back = Double.valueOf(summary.getBack());
            }
            DecimalFormat decimalFormat = new DecimalFormat("#.00");
            String result=   decimalFormat.format((back/push)*100);
//            setCellValue(row, 0, style, "" + summary.getOrgName());
            if (summary.getBack()==null){
                summary.setBack("0");
            }
            if (result.equals(".00")){
                result="0.00";
            }
            if (result.charAt(0)=='.'){
                result = 0+result;
            }
            setCellValue(row, 0, style, "" + summary.getDoctorName()); //医生姓名
            setCellValue(row, 1, style, "" + summary.getPush()); //推送零
            setCellValue(row, 2, style, ""+summary.getBack()); //回填量
            setCellValue(row, 3, style, ""+result);
            setCellValue(row,4,style,""+summary.getC());  //满意度百分比
            setCellValue(row,5,style,""+summary.getD()); //基本满意
            setCellValue(row,6,style,""+summary.getE()); //不满意百分比


            List<SurveyPlanTitleListResponse> listResponses = list.get(i).getSurveyPlanTitleListResponses();
            if(listResponses.size()>0) {
                for (int j = 0; j < listResponses.size(); j++) {
                    SurveyPlanTitleListResponse surveyPlanTitle = listResponses.get(j);
                    BigDecimal num = new BigDecimal(surveyPlanTitle.getAverage());
                    BigDecimal bnum = num.setScale(2, RoundingMode.HALF_UP);
                    setCellValue(row, j+4, style, bnum.toString());
                }
            }

//            if (CommonUtils.isNotEmpty(summary.getLogCdate())) {
//                setCellValue(row, 5, style, DateUtils.toString(summary.getLogCdate(), "yyyy-MM-dd HH:mm:ss") + "");
//            } else {
//                setCellValue(row, 5, style, "");
//            }
//            if (CommonUtils.isNotEmpty(summary.getLogClentOS())) {
//                setCellValue(row, 6, style, "" + summary.getLogClentOS());
//            } else {
//                setCellValue(row, 6, style, "");
//            }
//            setCellValue(row, 7, style, summary.getLogIp() + "");
//            setCellValue(row, 8, style, summary.getLogDesc() + "");
           /* setCellValue(row, 7, style, ""+summary.getVideoTotalFee());
            setCellValue(row, 8, style, summary.getReturnVisitCount()+"");
            setCellValue(row, 9, style, ""+summary.getReturnVisitTotalFee());
            setCellValue(row, 10, style, summary.getAssistClinicCount()+"");
            setCellValue(row, 11, style, ""+summary.getAssistClinicTotalFee());
            setCellValue(row, 12, style, String.valueOf(summary.getTotalFee()));
            setCellValue(row, 13, style, String.valueOf(summary.getAvgScore()));*/
        }
    }

    private static String numChangeString(Double num) {
        DecimalFormat df = new DecimalFormat("0.00");//格式化小数，不足的补0
        String numStr = df.format(num);//返回的是String类型的
        return numStr;
    }

    private static void initHeadContent(List<SurveyPlanWorkListResponse> list, Sheet sheet) {
        Row row0 = sheet.createRow(0);
        Row row2 = sheet.createRow(2);
        Row row3 = sheet.createRow(3);
        String title = "满意度评价统计";

        setCellValue(row0, 0, style, title);

//        setCellValue(row2, 0, style, "院区");
        setCellValue(row2, 0, style, "科室归属");
        setCellValue(row2, 1, style, "科室");
        setCellValue(row2, 2, style, "推送量");
        setCellValue(row2, 3, style, "回填量");
        setCellValue(row2, 4, style, "回填百分比");
        setCellValue(row2, 5, style, "满意度百分比");
        setCellValue(row2,6,  style,"基本满意百分比");
        setCellValue(row2,7,  style,  "不满意百分比");


        if(list.size()>0) {
            List<SurveyPlanTitleListResponse> listResponses = list.get(0).getSurveyPlanTitleListResponses();
            if(listResponses.size()>0) {
                for (int i = 0; i < listResponses.size(); i++) {
                    SurveyPlanTitleListResponse surveyPlanTitle = listResponses.get(0);
                    String[] data = surveyPlanTitle.getTitleName().split("<p>");
                    setCellValue(row2, i+4, style, data[1].split("</p>")[0]);
                }
            }
        }
//        setCellValue(row2, 4, style, "操作");
//        setCellValue(row2, 5, style, "操作时间");
//        setCellValue(row2, 6, style, "设备信息");
//        setCellValue(row2, 7, style, "IP地址");
//        setCellValue(row2, 8, style, "备注");
    }

    private static void initHeadStyle(Sheet sheet, int listSize) {
        sheet.setColumnWidth(0, 15 * 256); // 设置宽度
//        sheet.setColumnWidth(7, 15 * 300); // 设置宽度
//        sheet.setColumnWidth(8, 20 * 400); // 设置宽度

        // 合并单元格
        //sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 6));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 7));
        //sheet.addMergedRegion(new CellRangeAddress(3 + listSize, 3 + listSize, 0, 9));

        //合计的前三列要合并
//        if (listSize > 0) {
//            sheet.addMergedRegion(new CellRangeAddress(2 + listSize, 2 + listSize, 0, 2));
//        }

    }

    private static void setCellValue(Row row, int column, CellStyle style, String value) {
        Cell cell = row.createCell(column);
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }

    private static void initCellStyle(Workbook wk) {
        style = wk.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.forInt(1)); //垂直
        style.setAlignment(HorizontalAlignment.forInt(2)); //水平
        style.setWrapText(true); //自动换行
    }

    public static void main(String[] args) throws IOException {
            Double back;

            back = Double.valueOf("1");
            Double push= Double.valueOf("193");
        DecimalFormat decimalFormat = new DecimalFormat("#.00");
        String result=   decimalFormat.format((back/push)*100);
        if (result.equals(".00")){
            result="0.00";
        }


    }



    public static byte[] getExcelAsByteArray2(List<SurveyPlanWorkListResponse> list) {
        if (list == null) {
            throw new NullPointerException();
        }
        Workbook wb;
        ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("满意度评价");

            initCellStyle(wb);
            initHeadStyle(sheet, list.size());
            initHeadContent2(list, sheet);
            initBody2(list, sheet);

            wb.write(out);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return out.toByteArray();
    }


    private static void initHeadContent2(List<SurveyPlanWorkListResponse> list, Sheet sheet) {
        Row row0 = sheet.createRow(0);
        Row row2 = sheet.createRow(2);
        Row row3 = sheet.createRow(3);
        String title = "满意度医生评价统计";

        setCellValue(row0, 0, style, title);

//        setCellValue(row2, 0, style, "院区");
        setCellValue(row2, 0, style, "医生");
        setCellValue(row2, 1, style, "推送量");
        setCellValue(row2, 2, style, "回填量");
        setCellValue(row2, 3, style, "回填百分比");
        setCellValue(row2, 4, style, "满意度百分比");
        setCellValue(row2,5,style,"基本满意百分比");
        setCellValue(row2,6,style,"不满意百分比");
        if(list.size()>0) {
            List<SurveyPlanTitleListResponse> listResponses = list.get(0).getSurveyPlanTitleListResponses();
            if(listResponses.size()>0) {
                for (int i = 0; i < listResponses.size(); i++) {
                    SurveyPlanTitleListResponse surveyPlanTitle = listResponses.get(0);
                    String[] data = surveyPlanTitle.getTitleName().split("<p>");
                    setCellValue(row2, i+4, style, data[1].split("</p>")[0]);
                }
            }
        }
//        setCellValue(row2, 4, style, "操作");
//        setCellValue(row2, 5, style, "操作时间");
//        setCellValue(row2, 6, style, "设备信息");
//        setCellValue(row2, 7, style, "IP地址");
//        setCellValue(row2, 8, style, "备注");
    }


}
